package emp;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;

import booroo.utils.OracleJDBC;
import emp.empVO;

public class empDAO implements empService{
	private static final String insertSQL = "insert into employees values(empno=?,deptno=?,ename=?,sal=?,mgr=?,job=?";
	private static final String selectSQL = "select e.empno,e.ename,s.sheltername,d.dname,e.job from employees e,shelters s,departments d where e.sno=s.sno and e.deptno=d.deptno";
	private static final String selectDetailSQL = "select e.empno,s.sheltername,d.dname,e.ename,e.sal,e1.ename,to_char(e.hiredate,'yyyy/mm/dd'),"
			+ " e.job,e.birth,e.addno,e.ssn,e.phone from employees e,employees e1,shelters s,departments d where e.empno=? and e.sno=s.sno and e.deptno=d.deptno and e.mgr=e1.empno";
	private static final String selectEmpnoSQL = "select e.empno,e.ename,s.sheltername,d.dname,e.job from employees e,shelters s,departments d where e.sno=s.sno and e.deptno=d.deptno"
			+ " and e.empno = ?";
	private static final String selectEnameSQL = "select e.empno,e.ename,s.sheltername,d.dname,e.job from employees e,shelters s,departments d where e.sno=s.sno and e.deptno=d.deptno"
			+ " and e.ename like ?";
	private static final String selectSnameSQL = "select e.empno,e.ename,s.sheltername,d.dname,e.job from employees e,shelters s,departments d where e.sno=s.sno and e.deptno=d.deptno"
			+ " and s.sheltername like ?";
	private static final String selectDnameSQL = "select e.empno,e.ename,s.sheltername,d.dname,e.job from employees e,shelters s,departments d where e.sno=s.sno and e.deptno=d.deptno"
			+ " and d.dname like ?";
	private static final String selectJobSQL = "select e.empno,e.ename,s.sheltername,d.dname,e.job from employees e,shelters s,departments d where e.sno=s.sno and e.deptno=d.deptno"
			+ " and e.job like ?";
	private static final String selectSnoSQL = "select sno from shelters where sheltername = ?";
	private static final String selectDeptnoSQL = "select deptno from departments where dname = ?";
	private static final String selectMgrSQL = "select empno from employees where ename = ?";
	private static final String updateSQL = "update employees set "
			+ " sno=?,deptno=?,sal=?,mgr=?,job=?,phone=? where empno=?";
	private static final String deleteSQL = "delete from employee where empno=?";
		
	
	

	@Override
	public int insertEmp(empVO ev) {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public ArrayList<empVO> selectEmp() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		ArrayList<empVO> data = new ArrayList<>();
		
		
		try{
		conn = OracleJDBC.openConn();
		ps = conn.prepareStatement(selectSQL);
		rs = ps.executeQuery();
		
		while(rs.next()){
			empVO ev = new empVO(rs.getString(1),rs.getString(2),rs.getString(3),
					rs.getString(4),rs.getString(5));
			data.add(ev);
		}
		}catch(Exception ex){}finally{
			OracleJDBC.closeConn(conn, ps, rs);
		}
		return data;
	}

	

	@Override
	public empVO selectDetailEmp(String empno) {
		empVO ev = null;
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			conn = OracleJDBC.openConn();
			ps = conn.prepareStatement(selectDetailSQL);
			ps.setString(1, empno);
			rs = ps.executeQuery();
			
			while(rs.next()){
				ev = new empVO(rs.getString(1),rs.getString(2),rs.getString(3),
						rs.getString(4),rs.getString(5),rs.getString(6),rs.getString(7),rs.getString(8),rs.getString(9),rs.getString(10),rs.getString(11),rs.getString(12));
				
			}
			}catch(Exception ex){}finally{
				OracleJDBC.closeConn(conn, ps, rs);
			}
			return ev;
		}
	
	@Override
	public ArrayList<empVO> selectFindEmp(int title, String keyword) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		ArrayList<empVO> data = new ArrayList<>();
		
		
		try{
		conn = OracleJDBC.openConn();
		switch(title){
		case 1 :ps = conn.prepareStatement(selectEmpnoSQL); ps.setString(1, keyword); break;
		case 2 :ps = conn.prepareStatement(selectEnameSQL); ps.setString(1, "%" + keyword + "%"); break;
		case 3 :ps = conn.prepareStatement(selectSnameSQL); ps.setString(1, "%" + keyword + "%"); break;
		case 4 :ps = conn.prepareStatement(selectDnameSQL); ps.setString(1, "%" + keyword + "%"); break;
		case 5 :ps = conn.prepareStatement(selectJobSQL); ps.setString(1, "%" + keyword + "%"); break;
				}
		
		rs = ps.executeQuery();
		
		while(rs.next()){
			empVO ev = new empVO(rs.getString(1),rs.getString(2),rs.getString(3),
					rs.getString(4),rs.getString(5));
			data.add(ev);
		}
		}catch(Exception ex){}finally{
			OracleJDBC.closeConn(conn, ps, rs);
		}
		return data;
	}
	
	
	
	@Override
	public String selectSnameEmp(String sname) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String result = "";
		try{
			conn = OracleJDBC.openConn();
			ps = conn.prepareStatement(selectSnoSQL);
			ps.setString(1, sname);
			rs = ps.executeQuery();
			while(rs.next()){
			result = rs.getString(1);
			}
		}catch(Exception ex){}finally{
			OracleJDBC.closeConn(conn, ps, rs);
		}
		return result;
	}

	@Override
	public String selectDnameEmp(String dname) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String result = "";
		try{
			conn = OracleJDBC.openConn();
			ps = conn.prepareStatement(selectDeptnoSQL);
			ps.setString(1, dname);
			rs = ps.executeQuery();
			while(rs.next()){
			result = rs.getString(1);
			}
		}catch(Exception ex){}finally{
			OracleJDBC.closeConn(conn, ps, rs);
		}
		return result;
	}

	@Override
	public String selectMgrEmp(String mgr) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String result = "";
		try{
			conn = OracleJDBC.openConn();
			ps = conn.prepareStatement(selectMgrSQL);
			ps.setString(1, mgr);
			rs = ps.executeQuery();
			while(rs.next()){
			result = rs.getString(1);
			}
		}catch(Exception ex){}finally{
			OracleJDBC.closeConn(conn, ps, rs);
		}
		return result;
	}

	@Override
	public int updateEmp(empVO ev) {
		Connection conn = null;
		PreparedStatement ps = null;
		int cnt = 0;
		try{
			conn = OracleJDBC.openConn();
			ps = conn.prepareStatement(updateSQL);
			ps.setString(1, ev.getSname());
			ps.setString(2, ev.getDname());
			ps.setString(3, ev.getSal());
			ps.setString(4, ev.getMgr());
			ps.setString(5, ev.getJob());
			ps.setString(6, ev.getPhone());
			ps.setString(7, ev.getEmpno());
			cnt = ps.executeUpdate();
			
			
			}catch(Exception ex){}finally{
				OracleJDBC.closeConn(conn, ps, null);
			}
			return cnt;
		
	}

	@Override
	public int deleteEmp(String key) {
		// TODO Auto-generated method stub
		return 0;
	}

	
	
	
}
